﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SQLite;
using System.Data;

namespace SQLiteTesting
{
    class Program
    {
        static void Main(string[] args)
        {
            //Startup
            _startProg();

            string strDBFileName = @"c:\Tools\Programmierung\CSharp\Programs\KocInformant\KocInformant\bin\Debug\KocInformant.s3db";
            Console.WriteLine("DB File: " + strDBFileName + Environment.NewLine + "File exists: " + File.Exists(strDBFileName).ToString());


            #region SQLite test
            SQLiteConnection _CON = null;
            if (File.Exists(strDBFileName))
            {
                Console.WriteLine("Opening db ...");
                //Setting Cache to 600MB for performance reasons
                //Testphase
                _CON = new SQLiteConnection(String.Concat(
                    "Data Source=", strDBFileName, ";",
                    "Cache Size=600000;",
                    "Page Size=1024"));
                try
                {
                    _CON.Open();
                    Console.WriteLine("Successfully opened db ...");
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error opening db: " + ex.Message);
                }





                //create parameters
                var slpUserId = new SQLiteParameter() { ParameterName = "@" + "UserIdParam", DbType = DbType.Int64, Value = Convert.ToInt64(987654) };
                var slpFbUId = new SQLiteParameter() { ParameterName = "@" + "FbUIdParam", DbType = DbType.Int64, Value = Convert.ToInt64(123456) };
                var slpName = new SQLiteParameter() { ParameterName = "@" + "NameParam", DbType = DbType.StringFixedLength, Size = 64, Value = "TestName" };
                var slpForeName = new SQLiteParameter() { ParameterName = "@" + "ForeNameParam", DbType = DbType.StringFixedLength, Size = 64, Value = "Test" };
                var slpLastName = new SQLiteParameter() { ParameterName = "@" + "LastNameParam", DbType = DbType.StringFixedLength, Size = 64, Value = "Name" };
                var slpServerId = new SQLiteParameter() { ParameterName = "@" + "ServerIdParam", DbType = DbType.Int64, Value = 123 };
                var slpPrevtick = new SQLiteParameter() { ParameterName = "@" + "PrevtickParam", DbType = DbType.Int64, Value = 12345678 };
                var slpGold = new SQLiteParameter() { ParameterName = "@" + "GoldParam", DbType = DbType.Int64, Value = 10000000 };
                var slpFood = new SQLiteParameter() { ParameterName = "@" + "FoodParam", DbType = DbType.Int64, Value = (Int64)543000000000 };
                var slpWood = new SQLiteParameter() { ParameterName = "@" + "WoodParam", DbType = DbType.Int64, Value = (Int64)321000000000 };
                var slpStone = new SQLiteParameter() { ParameterName = "@" + "StoneParam", DbType = DbType.Int64, Value = (Int64)210000000000 };
                var slpOre = new SQLiteParameter() { ParameterName = "@" + "OreParam", DbType = DbType.Int64, Value = (Int64)100000000000 };
                var slpAetherstone = new SQLiteParameter() { ParameterName = "@" + "AetherstoneParam", DbType = DbType.Int64, Value = 1000000 };

                //Create command and add parameters
                String strCmdInfoTable = String.Concat(
                    "INSERT INTO tblInformation(",
                    "UserId, FbUId, NameInGame, ForeName, LastName, ServerId, PrevTick, ",
                    "SumGold, SumFood, SumWood, SumStone, SumOre, SumAetherstone) VALUES (",
                    "@UserIdParam, @FbUIdParam, @NameParam, @ForeNameParam, @LastNameParam, @ServerIdParam, @PrevtickParam, ",
                    "@GoldParam, @FoodParam, @WoodParam, @StoneParam, @OreParam, @AetherstoneParam);",
                    Environment.NewLine,
                    "SELECT last_insert_rowid() AS RecordID;");
                SQLiteCommand slcInformation = new SQLiteCommand(strCmdInfoTable, _CON);
                slcInformation.Parameters.AddWithValue("@UserIdParam", 987654);
                slcInformation.Parameters.AddWithValue("@FbUIdParam", 123456);
                slcInformation.Parameters.AddWithValue("@NameParam", "TestName");
                slcInformation.Parameters.AddWithValue("@ForeNameParam", "Test");
                slcInformation.Parameters.AddWithValue("@LastNameParam", "Name");
                slcInformation.Parameters.AddWithValue("@ServerIdParam", 123);
                slcInformation.Parameters.AddWithValue("@PrevtickParam", 12345678);
                slcInformation.Parameters.AddWithValue("@GoldParam", 10000000);
                slcInformation.Parameters.AddWithValue("@FoodParam",543000000000 );
                slcInformation.Parameters.AddWithValue("@WoodParam", 321000000000);
                slcInformation.Parameters.AddWithValue("@StoneParam", 210000000000);
                slcInformation.Parameters.AddWithValue("@OreParam",100000000000 );
                slcInformation.Parameters.AddWithValue("@AetherstoneParam", 1000000);
                //slcInformation.Parameters.Add(slpUserId);
                //slcInformation.Parameters.Add(slpFbUId);
                //slcInformation.Parameters.Add(slpName);
                //slcInformation.Parameters.Add(slpForeName);
                //slcInformation.Parameters.Add(slpLastName);
                //slcInformation.Parameters.Add(slpServerId);
                //slcInformation.Parameters.Add(slpPrevtick);
                //slcInformation.Parameters.Add(slpGold);
                //slcInformation.Parameters.Add(slpFood);
                //slcInformation.Parameters.Add(slpWood);
                //slcInformation.Parameters.Add(slpStone);
                //slcInformation.Parameters.Add(slpOre);
                //slcInformation.Parameters.Add(slpAetherstone);

                //submit command and receive newly created InformationID
                Int64 intInformationID = Convert.ToInt64(slcInformation.ExecuteScalar());
                Console.WriteLine("New tblInformation.ID: " + intInformationID.ToString());




                Console.WriteLine("Closing db ...");
                _CON.Close();
            }
            #endregion


            //finish
            _endProg();
        }



        static void _startProg()
        {
            Console.WriteLine("press any key to start program.");
            Console.WriteLine();
            Console.ReadKey();
        }

        static void _endProg()
        {
            Console.WriteLine();
            Console.WriteLine("press any key to finish program.");
            Console.ReadKey();
        }
    }
}
